﻿CREATE PROCEDURE [dbo].[utl_RemoveTimestamp]
@Table [sysname]=NULL
AS
BEGIN
	DECLARE @True  BIT; SET @True  = 1
	DECLARE @False BIT; SET @False = 0
	
	DECLARE @SQL                    NVARCHAR(MAX)
	DECLARE @ErrorCode              INT
	DECLARE @RowsAffected           INT
	DECLARE @Message                VARCHAR(MAX)
	DECLARE @DefaultConstraintName  sysname
	DECLARE @TimestampColumnName    sysname
	
	-- see if table name was passed
	IF @Table IS NULL
	BEGIN
	    SET @Message = 'Must pass table name.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 1
	END
	
	-- see if table exists
	IF dbo.fn_TableExists(@Table) = @False
	BEGIN
	    SET @Message = 'Table ''' + @Table + ''' not found.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 2
	END
	
	-- disable triggers for table
	SET @SQL = 'ALTER TABLE ' + @Table + ' DISABLE TRIGGER ALL'
	EXECUTE sp_executesql @SQL
	
	SELECT @ErrorCode = @@ERROR,
	       @RowsAffected = @@ROWCOUNT
	
	IF @ErrorCode <> 0
	BEGIN
	    SET @Message = 'Could not disable triggers for table ''' + @Table + '''.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 3
	END
	
	-- get name of timestamp column
	SET @TimestampColumnName = dbo.fn_TimestampColumn(@Table)
	
	-- drop timestamp column
	IF dbo.fn_ColumnExists(@Table, @TimestampColumnName) = @True
	BEGIN
	    -- drop column
	    SET @SQL = 'ALTER TABLE ' + @Table + ' DROP COLUMN ' + @TimestampColumnName
	    EXECUTE sp_executesql @SQL
	    
	    SELECT @ErrorCode = @@ERROR,
	           @RowsAffected = @@ROWCOUNT
	    
	    IF @ErrorCode <> 0
	    BEGIN
	        SET @Message = 'Could not drop column ''' + @Table + '.dbo.' + @TimestampColumnName + '.'''
	        RAISERROR(@Message, 16, 1)
	        RETURN 4
	    END
	END
	
	-- enable triggers for table
	SET @SQL = 'ALTER TABLE ' + @Table + ' ENABLE TRIGGER ALL'
	EXECUTE sp_executesql @SQL
	
	SELECT @ErrorCode = @@ERROR,
	       @RowsAffected = @@ROWCOUNT
	
	IF @ErrorCode <> 0
	BEGIN
	    SET @Message = 'Could not enable triggers for table ''' + @Table + '''.' 
	    RAISERROR(@Message, 16, 1)
	    RETURN 5
	END
	
	RETURN 0
END

